package com.opennews.openplatform.familyexpenseapi.service

import com.opennews.openplatform.familyexpenseapi.entity.Tables.*
import com.opennews.openplatform.familyexpenseapi.jooq.DSLContextWrapper
import org.jooq.DSLContext
import org.springframework.stereotype.Service
import org.springframework.transaction.annotation.Transactional

@Service
class AccountGroupUserService(
    private val dslContext: DSLContext,
    private val dslContextWrapper: DSLContextWrapper,
    private val userService: UserService,
    private val userActiveTokenService: UserActiveTokenService,
) {
    fun queryAvailableAccountGroups(userId: String): List<Map<String, Any?>> {
        // Fetches account group IDs for the given user
        val accountGroupIds = dslContext.selectFrom(ACCOUNT_GROUP_USER)
            .where(ACCOUNT_GROUP_USER.USER_ID.eq(userId))
            .fetch(ACCOUNT_GROUP_USER.ACCOUNT_GROUP_ID)

        // Queries the available account groups with additional user info
        return dslContext
            .select(
                ACCOUNT_GROUP_USER.USER_ID.`as`("id"),
                ACCOUNT_GROUP_USER.ACCOUNT_GROUP_ID.`as`("accountGroupId"),
                USER.USERNAME.`as`("username"),
                USER.FULL_NAME.`as`("fullName"),
                USER.AVATAR_FILENAME.`as`("avatarFilename"),
            )
            .from(ACCOUNT_GROUP_USER)
            .join(USER).on(ACCOUNT_GROUP_USER.USER_ID.eq(USER.ID))
            .where(
                ACCOUNT_GROUP_USER.ACCOUNT_GROUP_ID.`in`(accountGroupIds)
                    .and(ACCOUNT_GROUP_USER.IS_ACCOUNT_GROUP_CREATOR.isTrue())
            )
            .orderBy(USER.FULL_NAME.asc())
            .fetch()
            .map { p ->
                mapOf(
                    "id" to p["accountGroupId"],
                    "isOwner" to (p["id"] == userId),
                    "ownerId" to p["id"],
                    "ownerUsername" to p["username"],
                    "ownerFullName" to p["fullName"],
                    "ownerAvatarUrl" to userService.getAvatarUrl(p["avatarFilename"] as String?)
                )
            }
    }

    @Transactional
    fun remove(accountGroupId: String, userId: String, username: String) {
        // Removes user from this account group.
        dslContext.deleteFrom(ACCOUNT_GROUP_USER)
            .where(
                ACCOUNT_GROUP_USER.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .and(ACCOUNT_GROUP_USER.USER_ID.eq(userId))
                    .and(ACCOUNT_GROUP_USER.IS_ACCOUNT_GROUP_CREATOR.isFalse())
            )
            .execute()

        // Removes user roles in this account group.
        dslContext.deleteFrom(USER_ROLE)
            .where(
                USER_ROLE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .and(USER_ROLE.USER_ID.eq(userId))
            )
            .execute()

        // Checks if this is the user's default account group.
        if (dslContextWrapper.exists(
                USER, USER.ID.eq(userId)
                    .and(USER.ACCOUNT_GROUP_ID.eq(accountGroupId))
            )
        ) {
            // Sets user's own account group as default.
            userService.restoreAccountGroupByUserId(userId)

            // Deletes all tokens related to this account group to force a re-login.
            userActiveTokenService.deleteByUserAndAccountGroup(username, accountGroupId)
        }
    }
}